<?php

class Application_Model_Mysqli {

    public function obrir_bd() {
        $hostname = 'localhost';
        $username = 'root';
        $password = '';
        $database = 'pract_uf3';
        $mysqli = new mysqli($hostname, $username, $password, $database);
        if ($mysqli->connect_errno) {
            echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
        }
        return $mysqli;
    }

    public function tancar_bd($mysqli) {
        $mysqli->close();
    }

    /*     * ******************************* DEPARTAMENTS *********************************** */

    public function getDept($id_dept) {
        $link = $this->obrir_bd();
        if ($stmt = $link->prepare("SELECT id,nom,descripcio FROM dept where id=?")) {
            $stmt->bind_param("i", $id_dept);
            $stmt->execute();
            $stmt->bind_result($dept['id'], $dept['nom'], $dept['descripcio']);
            $stmt->fetch();
            $stmt->close();
        }
        $this->tancar_bd($link);
        return $dept;
    }

    public function addDept($n_dept, $d_dept) {
        $link = $this->obrir_bd();
        $sql = "INSERT INTO dept (nom,descripcio) VALUES (?,?)";
        if ($stmt = $link->prepare($sql)) {
            $stmt->bind_param("ss", $n_dept, $d_dept);
            $stmt->execute();
            $stmt->close();
        }
        $this->tancar_bd($link);
    }

    public function editDept($id_dept, $n_dept, $d_dept) {
        $link = $this->obrir_bd();
        $sql = "UPDATE dept SET nom =?, descripcio =? WHERE id=?";
        if ($stmt = $link->prepare($sql)) {
            $stmt->bind_param("ssi", $n_dept, $d_dept, $id_dept);
            $stmt->execute();
            $stmt->close();
        }
        $this->tancar_bd($link);
    }

    public function deleteDept($id_dept) {
        $link = $this->obrir_bd();
        mysqli_autocommit($link, FALSE);
        try {
            //esborrar empleats d'aquell dept
            $sql = "DELETE FROM emp WHERE id_dept=?";
            if ($stmt = $link->prepare($sql)) {
                $stmt->bind_param("i", $id_dept);
                $stmt->execute();
                $stmt->close();
            }

            $sql = "DELETE FROM dept WHERE id=?";
            if ($stmt = $link->prepare($sql)) {
                $stmt->bind_param("i", $id_dept);
                $stmt->execute();
                $stmt->close();
            }
            $link->commit();
        } catch (Exception $e) {
            $link->rollback();
        }
        $this->tancar_bd($link);
    }

    public function listDepts() {
        $i = 0;
        $depts = array();

        $link = $this->obrir_bd();
        $sql = "SELECT id,nom,descripcio FROM dept";
        if ($stmt = $link->prepare($sql)) {
            $stmt->execute();
            $stmt->bind_result($id, $nom, $desc);
            while ($stmt->fetch()) {
                $depts[$i] = array('id' => $id, 'nom' => $nom, 'descripcio' => $desc);
                $i++;
            }
            $stmt->close();
        }
        $this->tancar_bd($link);
        return $depts;
    }

    public function listDeptsSelect() {
        $depts = array();

        $link = $this->obrir_bd();
        $sql = "SELECT id,nom FROM dept";
        if ($stmt = $link->prepare($sql)) {
            $stmt->execute();
            $stmt->bind_result($id, $nom);
            while ($stmt->fetch()) {
                $depts[$id] = $nom;
            }
            $stmt->close();
        }
        $this->tancar_bd($link);
        return $depts;
    }

    /*     * ******************************* EMPS *********************************** */

    public function getEmp($id_emp) {
        $link = $this->obrir_bd();
        if ($stmt = $link->prepare("SELECT nom,cognom,dni,id_dept as departament FROM emp where id=?")) {
            $stmt->bind_param("i", $id_emp);
            $stmt->execute();
            $stmt->bind_result($emp['nom'], $emp['cognom'], $emp['dni'], $emp['departament']);
            $stmt->fetch();
            $stmt->close();
        }
        $this->tancar_bd($link);
        return $emp;
    }

    public function addEmp($nom_e, $cognom_e, $dni_e, $id_d) {
        $link = $this->obrir_bd();
        $sql = "INSERT INTO emp (nom,cognom,dni,id_dept) VALUES (?,?,?,?)";
        if ($stmt = $link->prepare($sql)) {
            $stmt->bind_param("sssi", $nom_e, $cognom_e, $dni_e, $id_d);
            $stmt->execute();
            $stmt->close();
        }
        $this->tancar_bd($link);
    }

    public function editEmp($id_e, $nom_e, $cognom_e, $dni_e, $id_d) {
        $link = $this->obrir_bd();
        $sql = "UPDATE emp SET nom =?, cognom =?,dni=?,id_dept=? WHERE id=?";
        if ($stmt = $link->prepare($sql)) {
            $stmt->bind_param("sssii", $nom_e, $cognom_e, $dni_e, $id_d, $id_e);
            $stmt->execute();
            $stmt->close();
        }
        $this->tancar_bd($link);
    }

    public function deleteEmp($id_e) {
        $link = $this->obrir_bd();
        $sql = "DELETE FROM emp WHERE id=?";
        if ($stmt = $link->prepare($sql)) {
            $stmt->bind_param("i", $id_e);
            $stmt->execute();
            $stmt->close();
        }
        $this->tancar_bd($link);
    }

    public function listEmps() {
        $i = 0;
        $emps = array();
        $link = $this->obrir_bd();
        $sql = "SELECT e.id,e.nom,e.cognom,e.dni,d.nom as departament FROM emp e, dept d WHERE e.id_dept=d.id";
        if ($stmt = $link->prepare($sql)) {
            $stmt->execute();
            $stmt->bind_result($id, $nom, $cognom, $dni, $dept);
            while ($stmt->fetch()) {
                $emps[$i] = array('id' => $id, 'nom' => $nom, 'cognom' => $cognom, 'dni' => $dni, 'departament' => $dept);
                $i++;
            }
            $stmt->close();
        }
        $this->tancar_bd($link);
        return $emps;
    }

}

